Download Administering a SQL Database Infrastructure.70-764.PassLeader.2018-11-23.239q.vcex

Vendor: Microsoft
Exam Code: 70-764
Exam Name: Administering a SQL Database Infrastructure
Date: Nov 23, 2018
File Size: 4 MB

How to open VCEX files?

Files with VCEX extension can be opened by ProfExam Simulator.

ProfExam Discount

Demo Questions

Question 1
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are examining information about users, sessions, and processed in an on-premises Microsoft SQL Server Database Engine instance. 
You need to return information about processes that are not idle, that belong to a specific user, or that belong to a specific session. 
What should you use?
  1. Activity Monitor
  2. sp_who3 stored procedure
  3. SQL Server Management Studio (SSMS) Object Explorer
  4. SQL Server Data Collector
  5. SQL Server Data Tools (SSDT)
  6. SQL Server Configuration Manager
Correct answer: B
Explanation:
Use sp_who3 to first view the current system load and to identify a session of interest. You should execute the query several times to identify which session id is most consuming teh system resources. Parameters sp_who3 null - who is active; sp_who3 1 or 'memory' - who is consuming the memory; sp_who3 2 or 'cpu' - who has cached plans that consumed the most cumulative CPU (top 10); sp_who3 3 or 'count' - who is connected and how many sessions it has; sp_who3 4 or 'idle' - who is idle that has open transactions; sp_who3 5 or 'tempdb' - who is running tasks that use tempdb (top 5); and, sp_who3 6 or 'block' - who is blocking.
Use sp_who3 to first view the current system load and to identify a session of interest. You should execute the query several times to identify which session id is most consuming teh system resources. 
Parameters 
sp_who3 null - who is active; 
sp_who3 1 or 'memory' - who is consuming the memory; 
sp_who3 2 or 'cpu' - who has cached plans that consumed the most cumulative CPU (top 10); 
sp_who3 3 or 'count' - who is connected and how many sessions it has; 
sp_who3 4 or 'idle' - who is idle that has open transactions; 
sp_who3 5 or 'tempdb' - who is running tasks that use tempdb (top 5); and, 
sp_who3 6 or 'block' - who is blocking.
Question 2
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You observe that several indexes are fragmented. 
You need to rebuild the indexes. 
What should you use?
  1. Activity Monitor
  2. sp_who3 stored procedure
  3. Object Explorer in the SQL Server Management Studio (SSMS)
  4. SQL Server Data Collector
  5. SQL Server Data Tools (SSDT)
  6. SQL Server Configuration Manager
Correct answer: C
Explanation:
How to: Rebuild an Index (SQL Server Management Studio)To rebuild an index In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, expand the database that contains the table with the specified index, and then expand Tables. Expand the table in which the index belongs and then expand Indexes. Right-click the index to rebuild and then click Rebuild. To start the rebuild operation, click OK. References: https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx
How to: Rebuild an Index (SQL Server Management Studio)
To rebuild an index 
  • In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. 
  • Expand Databases, expand the database that contains the table with the specified index, and then expand Tables. 
  • Expand the table in which the index belongs and then expand Indexes. 
  • Right-click the index to rebuild and then click Rebuild. 
  • To start the rebuild operation, click OK. 
References: https://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx
Question 3
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You need to examine information about logins, CPU times, and Disk I/O on a particular database in Microsoft Azure. 
What should you use?
  1. Activity Monitor
  2. sp_who3 stored procedure
  3. SQL Server Management Studio (SSMS) Object Explorer
  4. SQL Server Data Collector
  5. SQL Server Data Tools (SSDT)
  6. SQL Server Configuration Manager
Correct answer: A
Explanation:
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server. Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.The Activity User Tasks Pane shows information for active user connections to the instance, including the following column:* Login: The SQL Server login name under which the session is currently executing.The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column:* CPU (ms/sec): The rate of CPU use by the queryReferences: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
Activity Monitor displays information about SQL Server processes and how these processes affect the current instance of SQL Server. 
Activity Monitor is a tabbed document window with the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries.
The Activity User Tasks Pane shows information for active user connections to the instance, including the following column:
* Login: The SQL Server login name under which the session is currently executing.
The Recent Expensive Queries Pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds, including the following column:
* CPU (ms/sec): The rate of CPU use by the query
References: https://technet.microsoft.com/en-us/library/cc879320(v=sql.105).aspx
Question 4
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You collect performance metrics on multiple Microsoft SQL Server instances and store the data in a single repository. 
You need to examine disk usage, query statistics, and server activity without building custom counters. 
What should you use?
  1. Activity Monitor
  2. Sp_who3 stored procedure
  3. Object Explorer in the Microsoft SQL Server Management Studio (SSMS)
  4. SQL Server Data Collector
  5. SQL Server Data Tools (SSDT)
  6. SQL Server Configuration Manager
Correct answer: D
Explanation:
The data collector is a core component of the data collection platform for SQL Server 2017 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data
The data collector is a core component of the data collection platform for SQL Server 2017 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data
Question 5
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are examining information about users, sessions, and processes in an on-premises Microsoft SQL Server 2016 Standard Edition server. 
You need to identify waits for resources and return only the following information:
  • a list of all databases on the SQL Server instance, along with information about the database files, their paths, and names 
  • a list of the queries recently executed that use most of memory, disk, and network resources 
What should you use?
  1. Activity Monitor
  2. sp_who3 stored procedure
  3. SQL Server Management Studio (SSMS) Object Explorer
  4. SQL Server Data Collector
  5. SQL Server Data Tools (SSDT)
  6. SQL Server Configuration Manager
Correct answer: E
Explanation:
SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment for creating business intelligence solutions. SSDT features the Report Designer authoring environment, where you can open, modify, preview, save, and deploy Reporting Services paginated report definitions, shared data sources, shared datasets, and report parts. References: https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx
SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment for creating business intelligence solutions. SSDT features the Report Designer authoring environment, where you can open, modify, preview, save, and deploy Reporting Services paginated report definitions, shared data sources, shared datasets, and report parts. 
References: https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx
Question 6
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have an on-premises server that runs Microsoft SQL Server 2016 Standard Edition. 
You need to identify missing indexes. 
What should you use?
  1. Activity Monitor
  2. Sp_who3
  3. SQL Server Management Studio (SSMS) Object Explorer
  4. SQL Server Data Collector
  5. SQL Server Data Tools (SSDT)
  6. SQL Server Configuration Manager
Correct answer: D
Explanation:
Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity. The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance. Missing indexes can be found with the execution plans. References: https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/
Data Collector can gather performance information from multiple SQL Server instances and store it in a single repository. It has three built-in data collecting specifications (data collectors) designed to collect the most important performance metrics. The information collected by default is about disk usage, query statistics, and server activity. 
The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance. 
Missing indexes can be found with the execution plans. 
References: https://www.sqlshack.com/sql-server-performance-monitoring-data-collector/
Question 7
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a dedicated instance. The environments that you manage are shown in the following table. 
    
You need to monitor WingDB and gather information for troubleshooting issues. 
What should you use?
  1. sp_updatestats
  2. sp_lock
  3. sys.dm_os_waiting_tasks
  4. sys.dm_tran_active_snapshot_database_transactions
  5. Activity Monitor
Correct answer: B
Explanation:
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests. Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that are happening on your system. This procedure returns much of its information from the syslock info in the master database, which is a system table that contains information on all granted, converting, and waiting lock requests. 
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and later. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. 
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql
Question 8
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram. 
    
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table. 
    
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size. 
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. 
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes. 
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain. 
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1. 
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table. 
    
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION. 
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1. 
  • Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
  • Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table. 
    
You need to create a backup plan for Instance4. 
Which backup plan should you create?
  1. Weekly full backups, nightly differential. No transaction log backups are necessary.
  2. Weekly full backups, nightly differential backups, transaction log backups every 5 minutes.
  3. Weekly full backups, nightly differential backups, transaction log backups every 12 hours.
  4. Weekly full backups, nightly differential backups, nightly transaction log backups.
Correct answer: B
Explanation:
From scenario: Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. The recovery point objective of Instancse4 is 60 minutes. RecoveryPoint Objectives are commonly described as the amount of data that was lost during the outage and recovery period.References: http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives
From scenario: Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. The recovery point objective of Instancse4 is 60 minutes. RecoveryPoint Objectives are commonly described as the amount of data that was lost during the outage and recovery period.
References: http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives
Question 9
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram. 
    
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table. 
    
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size. 
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. 
Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes. 
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain. 
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1. 
All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table. 
    
 
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION. 
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1. 
  • Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.
  • Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.
The wait statistics monitoring requirements for the instances are described in the following table. 
    
You need to reduce the amount of time it takes to backup OperationsMain. 
What should you do?
  1. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.
  2. Modify the backup script to use the keyword SKIP in the WITH statement
  3. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
  4. Modify the full database backups script to stripe the backup across multiple backup files.
Correct answer: D
Explanation:
One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup. From scenario: Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup. 
From scenario: Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
Question 10
A Microsoft SQL Server database named DB1 has two filegroups named FG1 and FG2. You implement a backup strategy that creates backups for the filegroups. 
DB1 experiences a failure. You must restore FG1 and then FG2. 
You need to ensure that the database remains in the RECOVERING state until the restoration of FG2 completes. After the restoration of FG2 completes, the database must be online. 
What should you specify when you run the recovery command?
  1. the WITH NORECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
  2. the WITH RECOVERY clause for FG1 and the WITH RECOVERY clause for FG2
  3. the WITH RECOVERY clause for both FG1 and FG2
  4. the WITH NORECOVERY clause for both FG1 and FG2
Correct answer: A
HOW TO OPEN VCE FILES

Use VCE Exam Simulator to open VCE files
Avanaset

HOW TO OPEN VCEX AND EXAM FILES

Use ProfExam Simulator to open VCEX and EXAM files
ProfExam Screen

ProfExam
ProfExam at a 20% markdown

You have the opportunity to purchase ProfExam at a 20% reduced price

Get Now!